﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using NLite.Data.SqlTypes;
using NLite.Data;
using NLite.Data.Dialect.Function;

namespace NLite.Data.Dialect
{
    public class MySqlDialect : Dialect
    {
        private readonly TypeNames castTypeNames = new TypeNames();
       
        protected override void RegisterColumnTypes()
        {
            base.RegisterColumnTypes();

            //string type
            RegisterColumnType(DbTypeCode.AnsiStringFixedLength, "CHAR(255)");
            RegisterColumnType(DbTypeCode.AnsiStringFixedLength, 255, "CHAR($l)");
            RegisterColumnType(DbTypeCode.AnsiStringFixedLength, 65535, "TEXT");
            RegisterColumnType(DbTypeCode.AnsiStringFixedLength, 16777215, "MEDIUMTEXT");
            RegisterColumnType(DbTypeCode.AnsiString, "VARCHAR(255)");
            RegisterColumnType(DbTypeCode.AnsiString, 255, "VARCHAR($l)");
            RegisterColumnType(DbTypeCode.AnsiString, 65535, "TEXT");
            RegisterColumnType(DbTypeCode.AnsiString, 16777215, "MEDIUMTEXT");
            RegisterColumnType(DbTypeCode.StringFixedLength, "CHAR(255)");
            RegisterColumnType(DbTypeCode.StringFixedLength, 255, "CHAR($l)");
            RegisterColumnType(DbTypeCode.StringFixedLength, 65535, "TEXT");
            RegisterColumnType(DbTypeCode.StringFixedLength, 16777215, "MEDIUMTEXT");
            RegisterColumnType(DbTypeCode.String, "VARCHAR(255)");
            RegisterColumnType(DbTypeCode.String, 255, "VARCHAR($l)");
            RegisterColumnType(DbTypeCode.String, 65535, "TEXT");
            RegisterColumnType(DbTypeCode.String, 16777215, "MEDIUMTEXT");

            RegisterColumnType(DbTypeCode.Clob, "MEDIUMTEXT");
            RegisterColumnType(DbTypeCode.NClob, "MEDIUMTEXT");


            //binary type:
            RegisterColumnType(DbTypeCode.Binary, "LONGBLOB");
            RegisterColumnType(DbTypeCode.Binary, 127, "TINYBLOB");
            RegisterColumnType(DbTypeCode.Binary, 65535, "BLOB");
            RegisterColumnType(DbTypeCode.Binary, 16777215, "MEDIUMBLOB");
            RegisterColumnType(DbTypeCode.Blob, "MEDIUMBLOB");


            //Numeric type:
            RegisterColumnType(DbTypeCode.Boolean, "TINYINT(1)"); // SELECT IF(0, 'true', 'false');
            RegisterColumnType(DbTypeCode.Byte, "TINYINT UNSIGNED");
            RegisterColumnType(DbTypeCode.Currency, "MONEY");
            RegisterColumnType(DbTypeCode.Decimal, "NUMERIC(19,5)");
            RegisterColumnType(DbTypeCode.Decimal, 19, "NUMERIC($p, $s)");
            RegisterColumnType(DbTypeCode.Double, "DOUBLE");
            //The signed range is -32768 to 32767. The unsigned range is 0 to 65535. 
            RegisterColumnType(DbTypeCode.Int16, "SMALLINT");
            RegisterColumnType(DbTypeCode.Int32, "INTEGER"); //alias INT
            //As of MySQL 4.1, SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. 
            RegisterColumnType(DbTypeCode.Int64, "BIGINT");
            //!!!
            //Using FLOAT might give you some unexpected problems because all calculations in MySQL are done with double precision
            RegisterColumnType(DbTypeCode.Single, "FLOAT");
            //RegisterColumnType(DbTypeCode.Byte, 1, "BIT"); //Like TinyInt(i)
            RegisterColumnType(DbTypeCode.SByte, "TINYINT");

            //UNSINGED Numeric type:
            RegisterColumnType(DbTypeCode.UInt16, "SMALLINT UNSIGNED");
            RegisterColumnType(DbTypeCode.UInt32, "INTEGER UNSIGNED");
            RegisterColumnType(DbTypeCode.UInt64, "BIGINT UNSIGNED");
            //there are no other SqlTypeCode unsigned...but mysql support Float unsigned, double unsigned, etc..

            //Date and time type:
            RegisterColumnType(DbTypeCode.Date, "DATE");
            RegisterColumnType(DbTypeCode.DateTime, "DATETIME");
            RegisterColumnType(DbTypeCode.Time, "TIME");

            //special:
            RegisterColumnType(DbTypeCode.Guid, "VARCHAR(40)");

            RegisterCastTypes();
        }

        public override char CloseQuote
        {
            get
            {
                return '`';
            }
        }

        public override char OpenQuote
        {
            get
            {
                return '`';
            }
        }

//        
        protected override void RegisterStringFunction()
        {
            RegisterFunction(FunctionType.String.Lower, new StandardFunctionView("lower"));
            RegisterFunction(FunctionType.String.LTrim, new StandardFunctionView("ltrim"));
            RegisterFunction(FunctionType.String.RTrim, new StandardFunctionView("rtrim"));
            RegisterFunction(FunctionType.String.Trim, new StandardFunctionView("trim"));
            RegisterFunction(FunctionType.String.Upper, new StandardFunctionView("upper"));
            RegisterFunction(FunctionType.String.Length, new StandardFunctionView("length"));
            RegisterFunction(FunctionType.String.Left, new StandardFunctionView("left"));
            RegisterFunction(FunctionType.String.Right, new StandardFunctionView("right"));
            RegisterFunction(FunctionType.ToChar, new StandardFunctionView("to_char"));
            RegisterFunction(FunctionType.String.Concat, new StandardFunctionView("concat"));
            RegisterFunction(FunctionType.String.SubString, new StandardFunctionView("substr"));
            RegisterFunction(FunctionType.String.IndexOf, new FunctionTemplateView("instr(?1,?2)"));
            RegisterFunction(FunctionType.String.Remove, new RemoveFunctionView());
            RegisterFunction(FunctionType.String.Replace, new StandardFunctionView("replace"));

            RegisterFunction(FunctionType.Case, new CaseFunctionView());
            RegisterFunction(FunctionType.Cast, new CastFunctionView());
        }

        [Serializable]
        private class RemoveFunctionView : IFunctionView
        {
            private static readonly IFunctionView LocateWith2Params = new FunctionTemplateView("REPLACE(?1, SUBSTR(?1, ?2), '')");
            private static readonly IFunctionView LocateWith3Params = new FunctionTemplateView("REPLACE(?1, SUBSTR(?1, ?2, ?3), '')");

            public void Render(NodeWriter visitor, params INode[] args)
            {
                if (args == null)
                    throw new ArgumentNullException("args");
                if (args.Length != 2 && args.Length != 3)
                    throw new ArgumentException("'remove' function takes 2 or 3 arguments");
                if (args.Length == 2)
                    LocateWith2Params.Render(visitor, args);
                else
                    LocateWith3Params.Render(visitor, args);
            }
        }

        protected override void RegisterMathFunction()
        {
            RegisterFunction(FunctionType.Math.Abs, new StandardFunctionView("abs"));
            RegisterFunction(FunctionType.Math.Sign, new StandardFunctionView("sign"));
            RegisterFunction(FunctionType.Math.Acos, new StandardFunctionView("acos"));
            RegisterFunction(FunctionType.Math.Asin, new StandardFunctionView("asin"));
            RegisterFunction(FunctionType.Math.Atan, new StandardFunctionView("atan"));
            RegisterFunction(FunctionType.Math.Cos, new StandardFunctionView("cos"));
            RegisterFunction(FunctionType.Math.Cosh, new StandardFunctionView("cosh"));
            RegisterFunction(FunctionType.Math.Exp, new StandardFunctionView("exp"));
            RegisterFunction(FunctionType.Math.Log, new StandardFunctionView("log"));
            RegisterFunction(FunctionType.Math.Log10, new StandardFunctionView("log10"));
            RegisterFunction(FunctionType.Math.Sin, new StandardFunctionView("sin"));
            RegisterFunction(FunctionType.Math.Sinh, new StandardFunctionView("sinh"));
            RegisterFunction(FunctionType.Math.Sqrt, new StandardFunctionView("sqrt"));
            RegisterFunction(FunctionType.Math.Tan, new StandardFunctionView("tan"));
            RegisterFunction(FunctionType.Math.Tanh, new StandardFunctionView("tanh"));
            RegisterFunction(FunctionType.Math.Round, new StandardFunctionView("round"));
            RegisterFunction(FunctionType.Math.Truncate, new FunctionTemplateView("truncate(?1,0)"));
            RegisterFunction(FunctionType.Math.Ceiling, new StandardFunctionView("ceil"));
            RegisterFunction(FunctionType.Math.Floor, new StandardFunctionView("floor"));
            RegisterFunction(FunctionType.Math.Atan2, new StandardFunctionView("atan2"));
            RegisterFunction(FunctionType.Math.Power, new StandardFunctionView("pow"));

            RegisterFunction(FunctionType.Math.Mod, new VarArgsFunctionView("", " % ", ""));
        }

        protected override void RegisterDateFunction()
        {
            RegisterFunction(FunctionType.DateTime.Current, new NoArgFunctionView("now"));
            //RegisterFunction(FunctionType.DateTime.New, new NewDateFunctionView());
            RegisterFunction(FunctionType.DateTime.New, new FunctionTemplateView("CAST(?1 as DateTime)"));

            RegisterFunction(FunctionType.DateTime.Subtract, new StandardFunctionView("DATEDIFF"));//返回两个日期间隔的天数

            RegisterFunction(FunctionType.DateTime.GetSecond, new StandardFunctionView("Second"));
            RegisterFunction(FunctionType.DateTime.GetMinute, new StandardFunctionView("minute"));
            RegisterFunction(FunctionType.DateTime.GetHour, new StandardFunctionView("hour"));
            RegisterFunction(FunctionType.DateTime.GetDay, new StandardFunctionView("day"));
            RegisterFunction(FunctionType.DateTime.GetMonth, new StandardFunctionView("month"));
            RegisterFunction(FunctionType.DateTime.GetYear, new StandardFunctionView("month"));

            RegisterFunction(FunctionType.DateTime.GetMillisecond, new FunctionTemplateView("(MICROSECOND(?1)/1000)"));
            RegisterFunction(FunctionType.DateTime.GetDayOfWeek, new FunctionTemplateView("(DAYOFWEEK(?1)-1)"));
            RegisterFunction(FunctionType.DateTime.GetDayOfYear, new FunctionTemplateView("(DAYOFYEAR(?1)-1)"));

            RegisterFunction(FunctionType.DateTime.AddSecond, new FunctionTemplateView("DATE_ADD(?1, INTERVAL ?2 SECOND)"));
            RegisterFunction(FunctionType.DateTime.AddMinute, new FunctionTemplateView("DATE_ADD(?1, INTERVAL ?2 MINUTE)"));
            RegisterFunction(FunctionType.DateTime.AddHour, new FunctionTemplateView("DATE_ADD(?1, INTERVAL ?2 HOUR)"));
            RegisterFunction(FunctionType.DateTime.AddDay, new FunctionTemplateView("DATE_ADD(?1, INTERVAL ?2 DAY)"));
            RegisterFunction(FunctionType.DateTime.AddMonth, new FunctionTemplateView("DATE_ADD(?1, INTERVAL ?2 MONTH)"));
            RegisterFunction(FunctionType.DateTime.AddYear, new FunctionTemplateView("DATE_ADD(?1, INTERVAL ?2 YEAR)"));
        }

        //类型转换支持的力度很弱
        protected virtual void RegisterCastTypes()
        {
            RegisterCastType(DbTypeCode.AnsiString, "CHAR");
            RegisterCastType(DbTypeCode.AnsiStringFixedLength, "CHAR");
            RegisterCastType(DbTypeCode.String, "CHAR");
            RegisterCastType(DbTypeCode.StringFixedLength, "CHAR");
            RegisterCastType(DbTypeCode.Binary, "BINARY");

            RegisterCastType(DbTypeCode.Boolean, "UNSIGNED");

            RegisterCastType(DbTypeCode.Byte, "SIGNED");
            RegisterCastType(DbTypeCode.SByte, "SIGNED");
            RegisterCastType(DbTypeCode.Single, "Decimal");
            RegisterCastType(DbTypeCode.Double, "Decimal");
            RegisterCastType(DbTypeCode.Decimal, "Decimal");

            RegisterCastType(DbTypeCode.Int16, "SIGNED");
            RegisterCastType(DbTypeCode.Int32, "SIGNED");
            RegisterCastType(DbTypeCode.Int64, "SIGNED");
            RegisterCastType(DbTypeCode.UInt16, "UNSIGNED");
            RegisterCastType(DbTypeCode.UInt32, "UNSIGNED");
            RegisterCastType(DbTypeCode.UInt64, "UNSIGNED");
            RegisterCastType(DbTypeCode.Guid, "CHAR(40)");
            RegisterCastType(DbTypeCode.Time, "TIME");
            RegisterCastType(DbTypeCode.Date, "DATE");
            RegisterCastType(DbTypeCode.DateTime, "DATETIME");

            RegisterCastType(DbTypeCode.Clob, "CHAR");
            RegisterCastType(DbTypeCode.NClob, "CHAR");
            RegisterCastType(DbTypeCode.Blob, "BINARY");
        }

        /// <summary>
        /// Suclasses register a typename for the given type code, to be used in CAST()
        /// statements.
        /// </summary>
        /// <param name="code">The typecode</param>
        /// <param name="name">The database type name</param>
        protected void RegisterCastType(DbTypeCode code, string name)
        {
            castTypeNames.Put(code, name);
        }

        public override string GetTypeName(SqlType sqlType)
        {
            string result = castTypeNames.Get(sqlType.DbType);
            if (result == null)
            {
                throw new SqlParseExecption(string.Format("No CAST() type mapping for SqlType {0}", sqlType));
            }
            return result;
        }

        public override bool SupportsCollectionOperation(CollectionOperator type)
        {
            return false;
        }

        public override void BuildSelect(NodeWriter visitor, SelectNode select)
        {
            if (select == null)
                throw new ArgumentNullException("select");
            if (select.Into != null)
                throw new NotSupportedException("mysql not support 'insert into table2 select * from table1'");

            visitor.Builder.Append("SELECT ");

            if (select.IsDistinct)
                visitor.Builder.Append("DISTINCT ");
            if (select.Projections.Count > 0)
                visitor.VisitEnumerable(select.Projections.ToArray());
            else
                visitor.Builder.Append("*");

            visitor.Builder.AppendLine().Append("FROM ");

            var table = new TableNode(select.Name, select.Alias);
            visitor.Visit(table);

            if (select.Joins.Count() > 0)
            {
                visitor.Builder.Append(" ");
                visitor.VisitEnumerable(select.Joins.ToArray(), " ");
            }

            select.Where.Accept(visitor);
            select.Group.Accept(visitor);
            select.Having.Accept(visitor);
            select.OrderBy.Accept(visitor);

            if (select.PageSize > 0)
                visitor.Builder.AppendLine().Append("LIMIT ").Append(select.PageSize.ToString());
            if (select.PageIndex > 0)
                visitor.Builder.AppendLine().Append("OFFSET ").Append((select.PageIndex * select.PageSize + 1).ToString());
        }
    }
}
